package DataAccessLayer;

import DTO.Member.MemberList;
import DTO.Member.Members;
import java.math.BigInteger;
import java.sql.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class MembersSql extends DataLayerBase {

    public MembersSql() {
    }

    public boolean Insert(Members businessObject) {
        Connection con = getMainConnection();
        String sql = "{call dbo.[Members_Insert](?,?,?,?,?,?,?,?,?,?,?,?)}";
        CallableStatement cs = null;
        try {
            cs = con.prepareCall(sql);
            cs.registerOutParameter(1, java.sql.Types.INTEGER);
            cs.setString(2, businessObject.getMemCode());
            cs.setString(3, businessObject.getFullName());
            cs.setString(4, businessObject.getPhone());
            cs.setString(5, businessObject.getEmail());
            cs.setString(6, businessObject.getDateReg());
            cs.setString(7, businessObject.getAddress());
            cs.setString(8, businessObject.getNameSpace());
            cs.setInt(9, Integer.parseInt(businessObject.getAvaiableScore().toString()));
            cs.setInt(10, Integer.parseInt(businessObject.getHideScore().toString()));
            cs.setInt(11, Integer.parseInt(businessObject.getOriginalScore().toString()));
            cs.setString(12, businessObject.getNote());

            cs.executeUpdate();
            businessObject.setMemId(BigInteger.valueOf((int) cs.getInt(1)));
            //businessObject.MemId = (int) cs.getInt(1);
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                cs.close();
                con.close();

            } catch (SQLException ex) {
            }
        }
    }

    public boolean Update(Members businessObject) {
        Connection con = getMainConnection();
        CallableStatement cs = null;
        String sql = "{call dbo.[Members_Update](?,?,?,?,?,?,?,?,?,?,?,?)}";
        try {
            cs = con.prepareCall(sql);

            cs.setInt(1, Integer.parseInt(businessObject.getMemId().toString()));
            cs.setString(2, businessObject.getMemCode());
            cs.setString(3, businessObject.getFullName());
            cs.setString(4, businessObject.getPhone());
            cs.setString(5, businessObject.getEmail());
            cs.setString(6, businessObject.getDateReg());
            cs.setString(7, businessObject.getAddress());
            cs.setString(8, businessObject.getNameSpace());
            cs.setInt(9, Integer.parseInt(businessObject.getAvaiableScore().toString()));
            cs.setInt(10, Integer.parseInt(businessObject.getHideScore().toString()));
            cs.setInt(11, Integer.parseInt(businessObject.getOriginalScore().toString()));
            cs.setString(12, businessObject.getNote());

            cs.executeUpdate();
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                cs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(MembersSql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public Members SelectByPrimaryKey(int keys) {
        Connection con = getMainConnection();
        CallableStatement cs = null;
        ResultSet rs;
        String sql = "{call dbo.[Members_SelectByPrimaryKey](?)}";
        try {
            cs = con.prepareCall(sql);
            cs.setInt(1, keys);
            rs = cs.executeQuery();

            if (rs.next()) {
                Members businessObject = new Members();

                PopulateBusinessObjectFromReader(businessObject, rs);

                return businessObject;
            } else {
                return null;
            }
        } catch (Exception ex) {
            return null;
        } finally {
            try {
                cs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(MembersSql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public List<Members> SelectAll() {
        Connection con = getMainConnection();
        CallableStatement cs;
        ResultSet rs;
        String sql = "{call dbo.[Members_SelectAll]}";
        try {
            cs = con.prepareCall(sql);

            rs = cs.executeQuery();
            return PopulateObjectsFromReader(rs);
        } catch (Exception ex) {
            return null;
        } finally {
            try {
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(MembersSql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public List<Members> SelectByField(String fieldName, Object value) {
        Connection con = getMainConnection();
        CallableStatement cs = null;
        ResultSet rs;
        //String sql = "{call dbo.[Members_SelectByField](?,?)}";
        String sql = "{call dbo.Members_SelectByField(?,?)}";
        try {
            cs = con.prepareCall(sql);
            //cs.setString("@FieldName", fieldName);
            //cs.setObject("@Value", value);
            cs.setString(1, fieldName);
            cs.setObject(2, value);
            rs = cs.executeQuery();
            return PopulateObjectsFromReader(rs);

        } catch (Exception ex) {
            return null;
        } finally {
            try {
                cs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(MembersSql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public int GetMaxId() {
        Connection con = getMainConnection();
        try {
            ResultSet rs = null;
            Statement stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT MAX(MemId) FROM Members");
            rs.next();
            return rs.getInt(1);
        } catch (Exception ex) {
            return -1;
        } finally {
            try {
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(MembersSql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public List<Members> GetSubTree(String rootNamespace) {
        Connection con = getMainConnection();
        CallableStatement cs = null;
        ResultSet rs;
        String sql = "{call dbo.GetSubTree('" + rootNamespace + "')}";

        try {
            cs = con.prepareCall(sql);
            //cs.setString("?rootNamespace", rootNamespace);
            rs = cs.executeQuery();

            return PopulateObjectsFromReader(rs);
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            try {
                cs.close();
                con.close();
            } catch (SQLException ex) {
            }
        }
    }

    private void PopulateBusinessObjectFromReader(Members businessObject, ResultSet rs) {
        try {
            businessObject.setMemId(BigInteger.valueOf(rs.getInt("MemId")));
            //businessObject.MemId = rs.getInt("MemId");
        } catch (SQLException ex) {
        }
        try {
            businessObject.setMemCode(rs.getString("MemCode").trim());
            //businessObject.MemCode = rs.getString("MemCode");
        } catch (SQLException ex) {
            businessObject.setMemCode("");
        }
        try {
            businessObject.setFullName(rs.getString("FullName").trim());
        } catch (SQLException ex) {
            businessObject.setFullName("");
        }
        try {
            businessObject.setPhone(rs.getString("Phone").trim());
        } catch (Exception ex) {
            businessObject.setPhone("");
        }
        try {
            businessObject.setEmail(rs.getString("Email").trim());
        } catch (Exception ex) {
            businessObject.setEmail("");
        }
        try {
            businessObject.setDateReg(rs.getString("DateReg").trim());
        } catch (Exception ex) {
            businessObject.setDateReg("");
        }
        try {
            businessObject.setAddress(rs.getString("Address").trim());
        } catch (Exception ex) {
            businessObject.setAddress("");
        }
        try {
            businessObject.setNameSpace(rs.getString("NameSpace").trim());
        } catch (Exception ex) {
            businessObject.setNameSpace("");
        }
        try {
            businessObject.setAvaiableScore(BigInteger.valueOf(rs.getInt("AvaiableScore")));
        } catch (Exception ex) {
        }
        try {
            businessObject.setHideScore(BigInteger.valueOf(rs.getInt("HideScore")));
        } catch (Exception ex) {
        }
        try {
            businessObject.setOriginalScore(BigInteger.valueOf(rs.getInt("OriginalScore")));
        } catch (Exception ex) {
        }
        try {
            businessObject.setNote(rs.getString("Note").trim());
        } catch (Exception ex) {
            businessObject.setNote("");
        }
    }

    protected List<Members> PopulateObjectsFromReader(ResultSet rs) {
        try {
            MemberList list = new MemberList();
            while (rs.next()) {
                Members businessObject = new Members();
                PopulateBusinessObjectFromReader(businessObject, rs);
                list.addMember(businessObject);
            }
            return list.getMember();
        } catch (SQLException ex) {
            return null;
        }
    }
}
